package day03;

import entity.Emp;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import utils.DruidUtils;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class JdbcTemplateDemo {
    public static void main(String[] args) {
        JdbcTemplate jt = new JdbcTemplate(DruidUtils.ds);
        String sql_insert = "insert into demo values(null,'lahabru','123456')";
        jt.update(sql_insert);
        String sql_delete = "delete from demo where dname = 'teimis'";
        jt.update(sql_delete);
        String sql_update = "update demo set dpwd = '654321' where dname = 'cook'";
        jt.update(sql_update);
        String sql_select = "select * from demo";
        RowCallbackHandler rch = new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                System.out.println(resultSet.getString("dname"));
            }
        };
        jt.query(sql_select,rch);
    }
    @Test //课堂练习
    public void test1(){
        JdbcTemplate jt = new JdbcTemplate(DruidUtils.ds);
        String sql1 = "update emp set sal = 10000 where empno = ?";
        jt.update(sql1,7369);
        String sql2 = "insert into emp values(?,?,?,?,?,?,?,?)";
        int in = jt.update(sql2, 8000, "TEIMIS", "MANAGER", null, "1980-01-01", 20000, 5000, 40);
        System.out.println(in);
        String sql3 = "delete from emp where empno = 8000";
        int de = jt.update(sql3);
        System.out.println(de);
        String sql4 = "select * from emp where empno = ?";
        Map<String, Object> maps = jt.queryForMap(sql4, 7369);
        System.out.println(maps);
        String sql5 = "select * from emp";
        List<Map<String, Object>> list = jt.queryForList(sql5);
        System.out.println(list);
        String sql6 = "select * from emp";
        List<Emp> emps = jt.query(sql6, new BeanPropertyRowMapper<>(Emp.class));
        for (Emp emp : emps) {
            System.out.println(emp);
        }
        String sql7 = "select count(empno) from emp";
        //老师的做法
        Long count = jt.queryForObject(sql7, Long.class);
        System.out.println(count);
        //我的做法
        jt.query(sql7, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                System.out.println(resultSet.getInt(1));
            }
        });
    }
    @Test //课堂练习2，自定义表的CURD操作
    public void test2(){
        JdbcTemplate jt = new JdbcTemplate(DruidUtils.ds);
        String sql1 = "delete from demo where did = 118";
        int i1 = jt.update(sql1);
        System.out.println(i1);
        String sql2 = "update demo set dname = 'temis' where did = 117";
        int i2 = jt.update(sql2);
        System.out.println(i2);
        String sql3 = "insert into demo values(?,?,?)";
        int i3 = jt.update(sql3, null, "lahee", "741852");
        System.out.println(i3);
        String sql4 = "select dname from demo where did = 1";
        String name = jt.queryForObject(sql4, String.class);
        System.out.println(name);
        String sql5 = "select * from demo";
        List<Map<String, Object>> maps = jt.queryForList(sql5);
        System.out.println(maps);
        String sql6 = "select count(did) from demo";
        Integer count = jt.queryForObject(sql6, Integer.class);
        System.out.println(count);
    }
}
